const express = require('express');
const mysql2 = require('mysql2');

const router = express.Router();

// 创建数据库连接池
const pool = mysql2.createPool({
  host: 'localhost',
  user: 'root',
  database: 'ecut',
  waitForConnections: true,
  connectionLimit: 10,
  queueLimit: 0
});

const promisePool = pool.promise();

// GET `/student/list`
router.get('/student/list',(req,resp)=>{
    const sql = `SELECT id , name , gender , birthdate 
                 FROM t_students 
                 ORDER BY id ASC` ;
    promisePool.query(sql).then( ([rows,fields]) => {
        let localeVariables = {
            students: rows
        }
        // 第一个参数表示模板名称(view-name)
        // 第二个参数表示向模板传递的数据
        resp.render('list',localeVariables);
    }).catch( reason => {
        let localeVariables = {
            reason: reason
        }
        resp.render('list',localeVariables);
    });
});

// GET `/student/add`
router.get('/student/add',(req,resp)=>{
    resp.render('add');
});

// POST `/student/add`
router.post('/student/add',(req,resp)=>{
    // 解构赋值
    let { name , gender , birthdate } = req.body ;
    let sql = 'INSERT INTO t_students (name, gender, birthdate) VALUES (?, ?, ?)';
    let values = [ name , gender , birthdate ];
    promisePool.query( sql , values ).then( ([result,fields]) => {
        console.log( 'result: ' , result );
        // 重定向到学生列表
        resp.redirect('/student/list');
    }).catch( reason => {
        let localeVariables = {
            reason: reason
        }
        // 若发生错误则重新渲染 add 模板，让前台看到错误原因
        resp.render('add',localeVariables);
    });
});

// GET `/student/remove/:id`
router.get('/student/remove/:id',(req,resp)=>{
    let { id } = req.params ;
    let sql = `DELETE FROM t_students WHERE id = ?` ;
    let values = [ id ] ;
    promisePool.query( sql , values ).then( ([result,fields]) => {
        console.log( 'result: ' , result );
        // 操作成功后去往学生列表页面
        resp.redirect('/student/list');
    }).catch( reason => {
        resp.json( { success: false , message: reason } );
    });
});

// GET `/student/edit/:id`
router.get('/student/edit/:id',(req,resp)=>{
    const sql = `SELECT id , name , gender , birthdate 
                 FROM t_students 
                 WHERE id = ? ` ;
    promisePool.query( sql , [ req.params.id ] ).then( ([rows,fields]) => {
        console.log( rows );
        if( rows.length > 0 ) {
            let localeVariables = {
                student: rows[ 0 ]
            }
            resp.render('edit',localeVariables);
            return;
        }
        throw new Error('查询失败');
    }).catch( reason => {
        resp.render('edit',{ reason: reason } );
    });
});

// POST `/student/edit`
router.post('/student/edit',(req,resp)=>{
    console.log( req.body );
    let { id , name , gender , birthdate } = req.body;
    let sql = 'UPDATE t_students SET name = ? , gender = ? , birthdate = ? WHERE id = ? ';
    let values = [ name , gender , birthdate , id ];

    promisePool.query( sql , values ).then( ([result,fields]) => {
        console.log( 'result: ' , result );
        resp.redirect('/student/list');
    }).catch( reason => {
        resp.render('edit', { reason } );
    });
});

module.exports = router;